#!/usr/bin/env  python
#coding:UTF-8

# 多表自动备份ok。发送邮件失败。

import  MySQLdb
import  sys
import  os
import  datetime
import  smtplib
from    email.mime.text   import MIMEText
import  sys




# Define Mysql Environments
Hostname='192.168.0.141'
Username='root'
Password='123456'
Database='virtual'
MYSQLDUMP='/usr/bin/mysqldump'
GZIP='/usr/bin/gzip'
timestamp=datetime.datetime.now().strftime("%Y%m%d%H%M%S")
Destination_dir='/home/bak/tables/' + timestamp + '/'

# Define Smtp Environments
Host='smtp.exmail.qq.com'
Port=25
sender='yujianglei@singulax.com'
Pass='123456'
recivers='jianglei.yu@foxmail.com'

def main():
	if os.path.exists(Destination_dir) == False:
		os.makedirs(Destination_dir)
		db_table_backup()
	else:
		db_table_backup()

def email():
	try:
		server= smtplib.SMTP()
		server.connect(Host,Port)
		server.login(sender,Pass)
		server.sendmail(sender,recivers,msg.as_string())
	except Exception,e:
		print e
		print "邮件发送失败！"

def backup_failed():
	global msg
	msg = MIMEText('数据库单表备份失败')
	msg['subject'] = '数据库单表备份失败'
	msg['From'] = sender
	msg['To'] = recivers
	email()
	sys.exit(2)

def backup_sucess():
	global msg 
	msg = MIMEText(('数据库单表备份成功,共备份%d张表,共用时%.2f分钟.') % (tables_count,backup_period) ,  'plain','utf-8' ) 
	msg['subject'] = '数据库单表备份成功'  
	msg['From'] = sender
	msg['To'] = recivers
	email()
	

def db_table_backup():
	start_time=datetime.datetime.now()
	try:
		db = MySQLdb.connect(Hostname,Username,Password,Database,connect_timeout=2)
		cursor = db.cursor()
	except Exception, e:
		# print e
		print "连接数据库失败"
		backup_failed()
	cursor.execute('show tables')
	f = cursor.fetchall()
	list_status = []
	for table in f:
		# print table
		for i in table:
			MYSQLDUMP_CMD = MYSQLDUMP + ' -h' + Hostname + ' -u' + Username + ' -p' + Password + ' ' +  Database + ' ' + i + ' ' + '| ' + GZIP + ' >' + Destination_dir + Database + '-' + timestamp+ '.' + i + '.' + 'sql.gz'
			result = os.system(MYSQLDUMP_CMD)
			list_status.append(result)
	global tables_count
	tables_count = len(list_status)
	list_test=[0]
	j = set(list_status).issubset(set(list_test))
	if j == True:
		end_time=datetime.datetime.now()
		global backup_period
		backup_period = ((end_time - start_time).seconds)/60.0
		backup_sucess()
	else:
		backup_failed()

	cursor.close()
	db.close()


if __name__ ==  '__main__':
	main()


